-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:重建后的函数和原有函数参数数量不一致，部分合理报错
--step1: step1:;expect:
drop function if exists add_numbers(p_num1 int,p_num2 int);
CREATE FUNCTION add_numbers(
p_num1 INT,
p_num2 INT default 1
) RETURNS INT
AS $$
BEGIN
RETURN p_num1 + p_num2;
END;
$$ LANGUAGE plpgsql;
/
--step2: 创建视图，成功
drop view if exists v_ddl0043;
create view v_ddl0043 as select add_numbers(1,2);
--step3: 查询视图，成功
select * from v_ddl0043;
--step4: 删除函数
drop function if exists add_numbers(p_num1 int,p_num2 int);
--step5: 重建函数，参数数量不同且最后一个参数有默认值
CREATE FUNCTION add_numbers(
p_num1 INT,
p_num2 INT,
p_num3 INT DEFAULT 1
) RETURNS INT
AS $$
BEGIN
RETURN p_num1 + p_num2 + p_num3;
END;
$$ LANGUAGE plpgsql;
/
--step6: 查询视图，成功
select * from v_ddl0043;
--step7: 查询视图状态，有效
select valid from pg_object where object_oid='v_ddl0043'::regclass;
--step8: 查询视图定义，成功
select pg_get_viewdef('v_ddl0043');
--step9: 清理环境
drop view if exists v_ddl0043;
drop function if exists add_numbers(p_num1 int,p_num2 int,p_num3 int);
--step10: 创建表
drop table if exists numbers;
CREATE TABLE numbers (
num1 INT,
num2 INT,
num3 INT
);

--step11: 插入示例数据
INSERT INTO numbers (num1, num2, num3) VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9);
--step12: 创建函数，2个参数
drop function if exists calculate_sum;
CREATE function calculate_sum(a INT, b INT)
RETURNS INT AS $$
BEGIN

RETURN a + b;
END;
$$ LANGUAGE plpgsql;
/
--step13: 创建视图，依赖自定义函数
drop view if exists sum_view;
CREATE VIEW sum_view AS
SELECT
num1,
num2,
num3,
calculate_sum(num1, num2) AS sum_two_numbers FROM
numbers;
--step14: 删除函数，成功
drop function calculate_sum(a INT, b INT);
--step15: 查询视图，报错
select * from sum_view;
--step16: 查询视图状态，无效
select valid from pg_object where object_oid='sum_view'::regclass;
--step17: 重建函数3个参数;成功
CREATE FUNCTION calculate_sum(a INT, b INT, c INT)
RETURNS INT AS $$
BEGIN
RETURN a + b + c;
END;
$$ LANGUAGE plpgsql;
/
--step18: 查询视图，报错
select * from sum_view;
--step19: 查询视图状态，无效
select valid from pg_object where object_oid='sum_view'::regclass;
--step20: 重建函数，2个入参
CREATE function calculate_sum(a INT, b INT)
RETURNS INT AS $$
BEGIN

RETURN a + b;
END;
$$ LANGUAGE plpgsql;
/
--step21: 查询视图，成功
select * from sum_view;
--step22: 查询视图状态，有效
select valid from pg_object where object_oid='sum_view'::regclass;
--step23: 删除非视图依赖的函数
drop function calculate_sum(a INT, b INT, c INT);
--step24: 查询视图，成功
select * from sum_view;
--step25: 查询视图状态，有效
select valid from pg_object where object_oid='sum_view'::regclass;
--step26: 清理环境
drop table numbers;
drop view sum_view;
drop function calculate_sum(a INT, b INT);
